USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerShortStatsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerShortStatsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/10/08
-- Description:	PlayerShortStats Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerShortStatsSelectByPlayerIdLogId

			@PlayerId varchar(30),
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Ubers int,
		@Healing float,
		@Captures int,
		@FlagsCaptured int,
		@Dominations int
		
DECLARE @SQL varchar(600)
DECLARE @ResultSet TABLE (resultCount int)


SET @SQL = 'SELECT COUNT(AssistId) FROM Assists WHERE assister_PlayerId = ''' + @PlayerId + '''
	AND LogId in (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)

SELECT @Assists = resultCount FROM @ResultSet	


-- Player Name
SELECT @ReturnName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerID 
	GROUP BY PlayerName


-- KILLS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Attacker_PlayerId = ''' + @PlayerId + '''
			AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Kills = resultCount FROM @ResultSet


-- Deaths
SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @PlayerId + '''
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = ''medic'')
	AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Deaths = resultCount FROM @ResultSet

-- Captures
SET @SQL = 'SELECT COUNT(CaptureListId) FROM CaptureList, Captures 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND Captures.CaptureId = CaptureList.CaptureId
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Captures = resultCount FROM @ResultSet


-- Dominations

SET @SQL = 'SELECT COUNT(DominationId) FROM Dominations
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Dominations = resultCount FROM @ResultSet


-- FlagsCaptured

SET @SQL = 'SELECT COUNT(FlagId) FROM Flag 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = ''captured'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @FlagsCaptured = resultCount FROM @ResultSet


-- Ubers

SET @SQL = 'SELECT COUNT(ChargeId) FROM MedicCharge
				WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Ubers = resultCount FROM @ResultSet


SET @Healing = 0.0


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@Captures as [Captures], @FlagsCaptured as [FlagsCaptured],
		@PlayerId as [PlayerId]

END
GO

--      PlayerShortStatsSelectByPlayerIdLogId 'STEAM_0:1:100283', '1'


